-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_Insert]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_Update]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_Delete]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_Load]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_SelectAll]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_SelectBy_ProjectTypeID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_SelectBy_ProjectTypeID]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_SelectBy_TeamID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_SelectBy_TeamID]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_DeleteBy_ProjectTypeID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_DeleteBy_ProjectTypeID]

IF OBJECT_ID(N'[dbo].[p_ACTP_Project_DeleteBy_TeamID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_ACTP_Project_DeleteBy_TeamID]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_Insert]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_Insert]
	@ModuleID uniqueidentifier,
	@TeamID int,
	@ProjectTypeID int,
	@Code nvarchar(128),
	@Subject nvarchar(256),
	@StartDate datetime,
	@DueDate datetime,
	@CompletedDate datetime,
	@Priority char(1),
	@Status varchar(32),
	@CompletedPercent tinyint,
	@Description nvarchar(max),
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime,
	@ID int OUTPUT
AS

INSERT INTO [dbo].[t_ACTP_Projects]
(
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
)
VALUES 
(
	@ModuleID,
	@TeamID,
	@ProjectTypeID,
	@Code,
	@Subject,
	@StartDate,
	@DueDate,
	@CompletedDate,
	@Priority,
	@Status,
	@CompletedPercent,
	@Description,
	@CreatedBy,
	@CreatedDate,
	@UpdatedBy,
	@UpdatedDate
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_Update]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_Update]
	@ID int,
	@ModuleID uniqueidentifier,
	@TeamID int,
	@ProjectTypeID int,
	@Code nvarchar(128),
	@Subject nvarchar(256),
	@StartDate datetime,
	@DueDate datetime,
	@CompletedDate datetime,
	@Priority char(1),
	@Status varchar(32),
	@CompletedPercent tinyint,
	@Description nvarchar(max),
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS

UPDATE
	[dbo].[t_ACTP_Projects]
SET
	[ModuleID] = @ModuleID,
	[TeamID] = @TeamID,
	[ProjectTypeID] = @ProjectTypeID,
	[Code] = @Code,
	[Subject] = @Subject,
	[StartDate] = @StartDate,
	[DueDate] = @DueDate,
	[CompletedDate] = @CompletedDate,
	[Priority] = @Priority,
	[Status] = @Status,
	[CompletedPercent] = @CompletedPercent,
	[Description] = @Description,
	[CreatedBy] = @CreatedBy,
	[CreatedDate] = @CreatedDate,
	[UpdatedBy] = @UpdatedBy,
	[UpdatedDate] = @UpdatedDate
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_InsertUpdate]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_InsertUpdate]
	@ID int,
	@ModuleID uniqueidentifier,
	@TeamID int,
	@ProjectTypeID int,
	@Code nvarchar(128),
	@Subject nvarchar(256),
	@StartDate datetime,
	@DueDate datetime,
	@CompletedDate datetime,
	@Priority char(1),
	@Status varchar(32),
	@CompletedPercent tinyint,
	@Description nvarchar(max),
	@CreatedBy varchar(128),
	@CreatedDate datetime,
	@UpdatedBy varchar(128),
	@UpdatedDate datetime
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_ACTP_Projects] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_ACTP_Projects] 
		SET
			[ModuleID] = @ModuleID,
			[TeamID] = @TeamID,
			[ProjectTypeID] = @ProjectTypeID,
			[Code] = @Code,
			[Subject] = @Subject,
			[StartDate] = @StartDate,
			[DueDate] = @DueDate,
			[CompletedDate] = @CompletedDate,
			[Priority] = @Priority,
			[Status] = @Status,
			[CompletedPercent] = @CompletedPercent,
			[Description] = @Description,
			[CreatedBy] = @CreatedBy,
			[CreatedDate] = @CreatedDate,
			[UpdatedBy] = @UpdatedBy,
			[UpdatedDate] = @UpdatedDate
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_ACTP_Projects]
		(
			[ModuleID],
			[TeamID],
			[ProjectTypeID],
			[Code],
			[Subject],
			[StartDate],
			[DueDate],
			[CompletedDate],
			[Priority],
			[Status],
			[CompletedPercent],
			[Description],
			[CreatedBy],
			[CreatedDate],
			[UpdatedBy],
			[UpdatedDate]
		)
		VALUES 
		(
			@ModuleID,
			@TeamID,
			@ProjectTypeID,
			@Code,
			@Subject,
			@StartDate,
			@DueDate,
			@CompletedDate,
			@Priority,
			@Status,
			@CompletedPercent,
			@Description,
			@CreatedBy,
			@CreatedDate,
			@UpdatedBy,
			@UpdatedDate
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_Delete]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_Delete]
	@ID int
AS

DELETE FROM 
	[dbo].[t_ACTP_Projects]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_DeleteBy_ProjectTypeID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_DeleteBy_ProjectTypeID]
	@ProjectTypeID int
AS

DELETE FROM [dbo].[t_ACTP_Projects]
WHERE
	[ProjectTypeID] = @ProjectTypeID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_DeleteBy_TeamID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_DeleteBy_TeamID]
	@TeamID int
AS

DELETE FROM [dbo].[t_ACTP_Projects]
WHERE
	[TeamID] = @TeamID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_DeleteDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_ACTP_Projects] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_Load]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_Load]
	@ID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Projects]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_SelectBy_ProjectTypeID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_SelectBy_ProjectTypeID]
	@ProjectTypeID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Projects]
WHERE
	[ProjectTypeID] = @ProjectTypeID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_SelectBy_TeamID]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_SelectBy_TeamID]
	@TeamID int
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Projects]
WHERE
	[TeamID] = @TeamID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_SelectDynamic]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM [dbo].[t_ACTP_Projects] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_ACTP_Project_SelectAll]
-- Database: ASPNETVN.PORTAL
-- Author: Ngo Thanh Tung
-- Time created: Monday, October 04, 2010
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_ACTP_Project_SelectAll]
	@ModuleID UNIQUEIDENTIFIER
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[ModuleID],
	[TeamID],
	[ProjectTypeID],
	[Code],
	[Subject],
	[StartDate],
	[DueDate],
	[CompletedDate],
	[Priority],
	[Status],
	[CompletedPercent],
	[Description],
	[CreatedBy],
	[CreatedDate],
	[UpdatedBy],
	[UpdatedDate]
FROM
	[dbo].[t_ACTP_Projects]	
WHERE 
	(ModuleID = @ModuleID)

GO

